with  tmp_tab_reback_transfer_express as (
select
waybill_no as waybill_no
,named_struct(
'reback_apply_time',apply_time,
'reback_print_time',print_time,
'reback_apply_type_name',apply_type_name,
'reback_print_network_code',print_network_code,
'reback_apply_type_code',apply_type_code,
'reback_apply_network_name',apply_network_name,
'reback_apply_network_code',apply_network_code,
'is_reback',1
) as reback_cols
 from
jms_ods.tab_reback_transfer_express where dt='{{ execution_date | cst_ds }}' and date_format(apply_time,'yyyy-MM-dd') > date_add('{{ execution_date | cst_ds }}', -60)
),
tmp_tab_barscan_difficult as (
select
billcode as waybill_no
,named_struct(
'difficult_scantime',scantime,
'difficult_inputtime',inputtime,
'difficult_scantype',scantype,
'difficult_remark2',remark2,
'difficult_inputsite',inputsite,
'difficult_remark',remark,
'difficult_remark5',remark5,
'difficult_scansitecode',scansitecode,
'is_difficult',1
) as difficult_cols
from jms_ods.tab_barscan_difficult where dt between date_add('{{ execution_date | cst_ds }}', -60) and '{{ execution_date | cst_ds }}'
),
tmp_tab_end_piece as (
select
waybill_no as waybill_no
,named_struct(
'end_piece_end_time',end_time,
'end_piece_update_time',update_time,
'end_piece_end_code_desc',end_code_desc,
'end_piece_end_code',end_code,
'end_piece_input_time',input_time,
'end_piece_network_name',network_name,
'end_piece_source',source,
'end_piece_network_code',network_code,
'is_end_piece', 1
) as end_piece_cols
 from jms_ods.tab_end_piece where dt='{{ execution_date | cst_ds }}' and date_format(end_time,'yyyy-MM-dd') > date_add('{{ execution_date | cst_ds }}', -60)
),
tmp_yl_oms_interceptorpiece as (
select
waybill_no as waybill_no
,named_struct(
'interceptorpiece_registration_time',registration_time,
'interceptorpiece_cancal_time',cancal_time,
'interceptorpiece_scan_time',scan_time,
'interceptorpiece_intercept_time',intercept_time,
'interceptorpiece_type_name',type_name,
'interceptorpiece_status',status,
'interceptorpiece_intercept_network_name',intercept_network_name,
'interceptorpiece_cancal_network_code',cancal_network_code,
'interceptorpiece_cancal_network_name',cancal_network_name,
'interceptorpiece_scan_network_name',scan_network_name ,
'interceptorpiece_scan_network_code',scan_network_code,
'interceptorpiece_intercept_network_code',intercept_network_code,
'is_interceptorpiece',1
) as interceptorpiece_cols
from jms_ods.yl_oms_interceptorpiece where dt='{{ execution_date | cst_ds }}' and date_format(registration_time,'yyyy-MM-dd') > date_add('{{ execution_date | cst_ds }}', -60)
),
tmp__oms_waybill_incre_dt as (
select waybill_no, dt from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt where dt >= date_add('{{ execution_date | cst_ds }}',-60)
),
tmp_waybill_issue as (
select
waybill_no
,max(reback_apply_time)
,max(reback_apply_type_name)
,max(reback_print_network_code)
,max(reback_print_time)
,max(reback_apply_type_code)
,max(reback_apply_network_name)
,max(is_reback)
,max(difficult_scantime)
,max(difficult_scantype)
,max(difficult_remark2)
,max(difficult_inputsite)
,max(difficult_inputtime)
,max(difficult_remark)
,max(difficult_remark5)
,max(is_difficult)
,max(end_piece_input_time)
,max(end_piece_end_code_desc)
,max(end_piece_end_code)
,max(end_piece_network_name)
,max(end_piece_end_time)
,max(end_piece_update_time)
,max(end_piece_source)
,max(is_end_piece)
,max(interceptorpiece_cancal_network_code)
,max(interceptorpiece_cancal_time)
,max(interceptorpiece_registration_time)
,max(interceptorpiece_type_name)
,max(interceptorpiece_status)
,max(interceptorpiece_scan_time)
,max(interceptorpiece_intercept_network_name)
,max(interceptorpiece_cancal_network_name)
,max(interceptorpiece_scan_network_name)
,max(interceptorpiece_scan_network_code)
,max(interceptorpiece_intercept_time)
,max(is_interceptorpiece)
 from (
select
 waybill_no
,cast(max(reback_cols).reback_apply_time as string) as reback_apply_time
,max(reback_cols).reback_apply_type_name as reback_apply_type_name
,max(reback_cols).reback_print_network_code as reback_print_network_code
,cast(max(reback_cols).reback_print_time as string) as reback_print_time
,cast(max(reback_cols).reback_apply_type_code as string) as reback_apply_type_code
,max(reback_cols).reback_apply_network_name as reback_apply_network_name
,max(reback_cols).is_reback as is_reback
,null as difficult_scantime
,null as difficult_scantype
,null as difficult_remark2
,null as difficult_inputsite
,null as difficult_inputtime
,null as difficult_remark
,null as difficult_remark5
,null as is_difficult
,null as end_piece_input_time
,null as end_piece_end_code_desc
,null as end_piece_end_code
,null as end_piece_network_name
,null as end_piece_end_time
,null as end_piece_update_time
,null as end_piece_source
,null as is_end_piece
,null as interceptorpiece_cancal_network_code
,null as interceptorpiece_cancal_time
,null as interceptorpiece_registration_time
,null as interceptorpiece_type_name
,null as interceptorpiece_status
,null as interceptorpiece_scan_time
,null as interceptorpiece_intercept_network_name
,null as interceptorpiece_cancal_network_name
,null as interceptorpiece_scan_network_name
,null as interceptorpiece_scan_network_code
,null as interceptorpiece_intercept_time
,null as is_interceptorpiece
from tmp_tab_reback_transfer_express group by waybill_no
union all
select
 waybill_no
,null as reback_apply_time
,null as reback_apply_type_name
,null as reback_print_network_code
,null as reback_print_time
,null as reback_apply_type_code
,null as reback_apply_network_name
,null as is_reback
,cast(max(difficult_cols).difficult_scantime as string) as difficult_scantime
,max(difficult_cols).difficult_scantype as difficult_scantype
,max(difficult_cols).difficult_remark2 as difficult_remark2
,max(difficult_cols).difficult_inputsite as difficult_inputsite
,cast(max(difficult_cols).difficult_inputtime as string) as difficult_inputtime
,max(difficult_cols).difficult_remark as difficult_remark
,max(difficult_cols).difficult_remark5 as difficult_remark5
,max(difficult_cols).is_difficult as is_difficult
,null as end_piece_input_time
,null as end_piece_end_code_desc
,null as end_piece_end_code
,null as end_piece_network_name
,null as end_piece_end_time
,null as end_piece_update_time
,null as end_piece_source
,null as is_end_piece
,null as interceptorpiece_cancal_network_code
,null as interceptorpiece_cancal_time
,null as interceptorpiece_registration_time
,null as interceptorpiece_type_name
,null as interceptorpiece_status
,null as interceptorpiece_scan_time
,null as interceptorpiece_intercept_network_name
,null as interceptorpiece_cancal_network_name
,null as interceptorpiece_scan_network_name
,null as interceptorpiece_scan_network_code
,null as interceptorpiece_intercept_time
,null as is_interceptorpiece
from tmp_tab_barscan_difficult group by waybill_no
union all
select
 waybill_no
,null as reback_apply_time
,null as reback_apply_type_name
,null as reback_print_network_code
,null as reback_print_time
,null as reback_apply_type_code
,null as reback_apply_network_name
,null as is_reback
,null as difficult_scantime
,null as difficult_scantype
,null as difficult_remark2
,null as difficult_inputsite
,null as difficult_inputtime
,null as difficult_remark
,null as difficult_remark5
,null as is_difficult
,cast(max(end_piece_cols).end_piece_input_time as string) as end_piece_input_time
,max(end_piece_cols).end_piece_end_code_desc as end_piece_end_code_desc
,max(end_piece_cols).end_piece_end_code as send_piece_end_code
,max(end_piece_cols).end_piece_network_name as end_piece_network_name
,cast(max(end_piece_cols).end_piece_end_time as string) as end_piece_end_time
,cast(max(end_piece_cols).end_piece_update_time as string) as end_piece_update_time
,cast(max(end_piece_cols).end_piece_source as string) as end_piece_source
,max(end_piece_cols).is_end_piece as is_end_piece
,null as interceptorpiece_cancal_network_code
,null as interceptorpiece_cancal_time
,null as interceptorpiece_registration_time
,null as interceptorpiece_type_name
,null as interceptorpiece_status
,null as interceptorpiece_scan_time
,null as interceptorpiece_intercept_network_name
,null as interceptorpiece_cancal_network_name
,null as interceptorpiece_scan_network_name
,null as interceptorpiece_scan_network_code
,null as interceptorpiece_intercept_time
,null as is_interceptorpiece
from tmp_tab_end_piece group by waybill_no
union all
select
 waybill_no
,null as reback_apply_time
,null as reback_apply_type_name
,null as reback_print_network_code
,null as reback_print_time
,null as reback_apply_type_code
,null as reback_apply_network_name
,null as is_reback
,null as difficult_scantime
,null as difficult_scantype
,null as difficult_remark2
,null as difficult_inputsite
,null as difficult_inputtime
,null as difficult_remark
,null as difficult_remark5
,null as is_difficult
,null as end_piece_input_time
,null as end_piece_end_code_desc
,null as end_piece_end_code
,null as end_piece_network_name
,null as end_piece_end_time
,null as end_piece_update_time
,null as end_piece_source
,null as is_end_piece
,max(interceptorpiece_cols).interceptorpiece_cancal_network_code as interceptorpiece_cancal_network_code
,cast(max(interceptorpiece_cols).interceptorpiece_cancal_time as string) as interceptorpiece_cancal_time
,cast(max(interceptorpiece_cols).interceptorpiece_registration_time as string) as interceptorpiece_registration_time
,max(interceptorpiece_cols).interceptorpiece_type_name as interceptorpiece_type_name
,cast(max(interceptorpiece_cols).interceptorpiece_status as string) as interceptorpiece_status
,cast(max(interceptorpiece_cols).interceptorpiece_scan_time as string) as interceptorpiece_scan_time
,max(interceptorpiece_cols).interceptorpiece_intercept_network_name as interceptorpiece_intercept_network_name
,max(interceptorpiece_cols).interceptorpiece_cancal_network_name as interceptorpiece_cancal_network_name
,max(interceptorpiece_cols).interceptorpiece_scan_network_name as interceptorpiece_scan_network_name
,max(interceptorpiece_cols).interceptorpiece_scan_network_code as interceptorpiece_scan_network_code
,cast(max(interceptorpiece_cols).interceptorpiece_intercept_time as string) as interceptorpiece_intercept_time
,max(interceptorpiece_cols).is_interceptorpiece as is_interceptorpiece
from tmp_yl_oms_interceptorpiece group by waybill_no
)tmp group by waybill_no
)
insert overwrite table jms_dwd.dwd_s04_abnormal_waybill_issuse_collection_dt partition(dt)
select
t1.*
,nvl(t2.dt,'2049-12-31') as dt
from tmp_waybill_issue t1 left join tmp__oms_waybill_incre_dt t2 on t1.waybill_no = t2.waybill_no;
